package com.hsnn.medstgmini.trading.pur.dao.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.trading.pur.dao.PurReturnOrderDao;
import com.hsnn.medstgmini.trading.pur.model.PurAoginfo;
import com.hsnn.medstgmini.trading.pur.model.PurReturnPlan;
import com.hsnn.medstgmini.trading.pur.model.ReturnOrder;
import com.hsnn.medstgmini.trading.pur.model.ReturnStatus;
import com.hsnn.medstgmini.trading.pur.model.form.ReturnDrugForm;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseNumber;
import com.hsnn.medstgmini.util.PinYin;

@Repository
public class PurReturnOrderDaoImpl implements PurReturnOrderDao {
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 获取退货订单数据[中心端] 应晓川
	 * @date 2015年7月14日
	 * @return
	 */
	@Override
	public Pagination getReturnOrderList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.ret_code retCode, a.hosp_code hospCode, a.hosp_name hospName, ");
		sb.append(" a.detail_count detailCount, a.detail_amount detailAmount,  ");
		sb.append(" DATE_FORMAT(a.ret_datetime, '%Y-%m-%d %T') retDatetime, ");
		sb.append(" a.hosp_ar_fullname areaName, a.del_comp_code delCompCode, ");
		sb.append(" a.del_comp_name delCompName, a.del_comp_name_spel, a.`status`, a.confirm_user confirmUser, ");
		sb.append(" DATE_FORMAT(a.confirm_time, '%Y-%m-%d %T') confirmTime, b.`name` retUser ");
		sb.append(" FROM pur_returninfo a  ");
		sb.append(" LEFT JOIN sys_user b ON a.ret_user = b.id ");
		sb.append(" WHERE a.`status` = :status ");
		
		ReturnOrder returnOrder = (ReturnOrder) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("status", ReturnStatus.submit.getKey());
		
		if (StringUtils.isNotBlank(returnOrder.getRetCode())) {
			sb.append(" AND a.ret_code = :retCode ");
			params.put("retCode", returnOrder.getRetCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospCode())) {
			sb.append(" AND a.hosp_code = :hospCode ");
			params.put("hospCode", returnOrder.getHospCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + returnOrder.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDelCompName())) {
			sb.append(" AND ( a.del_comp_name LIKE :delCompName OR a.del_comp_name_spel LIKE :delCompName ) ");
			params.put("delCompName", "%" + returnOrder.getDelCompName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", returnOrder.getStartDate());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", returnOrder.getEndDate());
		}
		sb.append(" order by a.ret_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}
	/**
	 * @category 获取退货订单数据[生产企业] 王炳
	 * @date 2015年7月14日
	 * @return
	 */
	@Override
	public Pagination getProCompReturnOrderList(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.ret_count retCount, a.ret_reason retReason, b.approve_code approveCode, ");
		sb.append(" 	b.qualityspec_name drugName, b.drug_code drugCode, b.form_name formName, ");
		sb.append(" 	b.spec_name specName, b.unit_name unitName, b.material_name materialName, ");
		sb.append(" 	b.factor, b.hosp_code hospCode, b.hosp_name hospName, b.ar_fullname areaName, ");
		sb.append(" 	b.del_comp_code delCompCode, b.del_comp_name delCompName, b.pro_price proPrice, ");
		sb.append(" 	b.total_amount totalAmount,a.drug_medicine drugMedicine, a.ret_code retCode, c.`name` updUser, ");
		sb.append(" 	DATE_FORMAT(a.upd_time, '%Y-%m-%d %T') updTime ");
		sb.append(" FROM pur_returninfodetail a  ");
		sb.append(" JOIN pur_aoginfo b ON a.recp_info_id = b.recp_info_id ");
		sb.append(" LEFT JOIN sys_user c ON a.upd_user = c.id ");
		sb.append(" WHERE a.`status` = :status AND b.prod_compcode = :compCode ");

		ReturnOrder returnOrder = (ReturnOrder) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("status", ReturnStatus.submit.getKey());
		params.put("compCode", returnOrder.getCompCode());
		
		if (StringUtils.isNotBlank(returnOrder.getDrugCode())) {
			sb.append(" AND b.drug_code = :drugCode ");
			params.put("drugCode", returnOrder.getDrugCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDrugName())) {
			sb.append(" AND ( b.qualityspec_name LIKE :drugName OR b.qualityspec_name_spel LIKE :drugName ) ");
			params.put("drugName", "%" + returnOrder.getDrugName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospCode())) {
			sb.append(" AND b.hosp_code = :hospCode ");
			params.put("hospCode", returnOrder.getHospCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospName())) {
			sb.append(" AND ( b.hosp_name LIKE :hospName OR b.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + returnOrder.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getAreaId())) {
			sb.append(" AND b.hosp_area_id LIKE :areaId ");
			params.put("areaId", AreaUtil.escapeAreaId(returnOrder.getAreaId()));
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDelCompName())) {
			sb.append(" AND ( b.del_comp_name LIKE :delCompName OR b.del_comp_name_spel LIKE :delCompName ) ");
			params.put("delCompName", "%" + returnOrder.getDelCompName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND DATE_FORMAT(a.upd_time, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", returnOrder.getStartDate());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND DATE_FORMAT(a.upd_time, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", returnOrder.getEndDate());
		}
		
		sb.append(" ORDER BY a.upd_time DESC ");
		
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取可退货药品数据（医疗机构）
	 * @author 韩守松
	 * @date 2015年12月1日 
	 * @param page
	 * @return
	 */
	public Pagination getReturnDrugApplyDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" SELECT a.recp_info_id as recpInfoId,a.user_id as userId,a.goods_code as goodsCode,a.drug_code as drugCode,a.qualityspec_name as qualityspecName,a.qualityspec_name_spel, ")
				.append(" a.form_name as FormName,a.spec_name as specName,a.factor,a.unit_name unitName,a.material_name as materialName, ")
				.append(" a.prod_compname as prodCompname,a.drug_medicine as drugMedicine,a.del_comp_code as delCompCode,a.del_comp_name as delCompName, ")
				.append(" a.approve_code as approveCode,a.credential,a.pro_price as proPrice,a.total_amount as totalAmount,a.prod_compcode as prodCompcode,a.return_count returnCount ")
				.append("  from pur_aoginfo a ")
				.append(" where a.hosp_code = :hospCode and a.return_count < a.total_amount ")
				.append(" and a.recp_info_id not in(select t.recp_info_id from pur_returnplan t where t.hosp_code = :hospCode)");
		Map<String, Object> params = new HashMap<String, Object>();
		ReturnDrugForm form = (ReturnDrugForm) page.getForm();
		params.put("hospCode", form.getHospCode());
		if (StringUtils.isNotBlank(form.getDrugCode())) {
			sql.append(" and a.drug_code=:drugCode");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getDrugName())) {
			if (PinYin.isPinYin(form.getDrugName().trim())) {
				sql.append(" and a.qualityspec_name_spel like :qualityspecName");
				params.put("qualityspecName", "%"
						+ form.getDrugName().trim().toUpperCase() + "%");
			} else {
				sql.append(" and a.qualityspec_name like :qualityspecName");
				params.put("qualityspecName", "%" + form.getDrugName().trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(form.getCompName())) {
			sql.append(" and (a.prod_compname like :compName or a.prod_compname_spel like :compSpelCode)");
			params.put("compName", "%" + form.getCompName().trim() + "%");
			params.put("compSpelCode", "%" + form.getCompName().trim().toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			sql.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompSpelCode)");
			params.put("delCompName", "%" + form.getDelCompName().trim() + "%");
			params.put("delCompSpelCode", "%" + form.getDelCompName().trim().toUpperCase() + "%");
		}
		sql.append(" order by a.upd_time desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取可制定退货单计划列表数据（医疗机构）
	 * @author 韩守松
	 * @date 2015年12月1日 
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getReturnDrugPlanDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" SELECT a.recp_info_id as recpInfoId,a.ret_plan_id as retPlanId ,a.user_id as userId, ")
				.append(" goods.goods_code,goods.drug_code drugCode,goods.qualityspec_name qualityspecName, ")
				.append(" goods.form_name formName,goods.spec_name specName,goods.qualityspec_status qualityspecStatus,goods.factor, ")
				.append(" goods.unit_name unitName,goods.material_name materialName,goods.prod_compcode prodCompcode,goods.prod_compname prodCompname,goods.drug_medicine drugMedicine,")
				.append(" a.del_comp_code as delCompCode,a.del_comp_name as delCompName, ")
				.append(" a.approve_code as approveCode,a.credential,a.pro_price as proPrice,a.ret_count as retCount,(a.pro_price*a.ret_count) as retMoney, ")
				.append(" goods.total_amount totalAmount,a.join_ret_time as joinRetTime,a.ret_reason as retReason,goods.return_count returnCount  ")
				.append(" from pur_returnplan a ")
				.append(" join  pur_aoginfo goods on a.recp_info_id = goods.recp_info_id ")
				.append(" where a.hosp_code = :hospCode  ");
		Map<String, Object> params = new HashMap<String, Object>();
		ReturnDrugForm form = (ReturnDrugForm) page.getForm();
		params.put("hospCode", form.getHospCode());
		if (StringUtils.isNotBlank(form.getDrugCode())) {
			sql.append(" and goods.drug_code=:drugCode");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getDrugName())) {
			if (PinYin.isPinYin(form.getDrugName().trim())) {
				sql.append(" and goods.qualityspec_name_spel like :drugName");
				params.put("drugName", "%"
						+ form.getDrugName().trim().toUpperCase() + "%");
			} else {
				sql.append(" and goods.qualityspec_name like :drugName");
				params.put("drugName", "%" + form.getDrugName().trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(form.getCompName())) {
			sql.append(" and (goods.prod_compname like :compName or goods.prod_compname_spel like :compSpelCode)");
			params.put("compName", "%" + form.getCompName().trim() + "%");
			params.put("compSpelCode", "%" + form.getCompName().trim().toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			sql.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompSpelCode)");
			params.put("delCompName", "%" + form.getDelCompName().trim() + "%");
			params.put("delCompSpelCode", "%" + form.getDelCompName().trim().toUpperCase() + "%");
		}
		sql.append(" order by a.join_ret_time desc");
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao
				.getSqlMapList(sql.toString(), params);
		page.setRows(list);
		return page;
	}

	/**
	 * 
	 * @category 根据到货信息ID查询到货信息
	 * @author 韩守松
	 * @date 2015年12月1日 
	 * @param id
	 * @return
	 */
	public PurAoginfo getAoginfoById(String id) {
		return (PurAoginfo) dao.getUniquerResult(PurAoginfo.class, id);
	}

	/**
	 * 
	 * @category 加入退货计划（医疗机构）
	 * @author 韩守松
	 * @date 2015年12月1日 
	 * @param list
	 */
	public void joinReturnPlan(List<ReturnDrugForm> list) {
		if (null != list && list.size() > 0) {
			for(int i=0;i<list.size();i++){
				ReturnDrugForm form = list.get(i);
				Session session =  sessionFactory.getCurrentSession();
				PurAoginfo aoginfo = getAoginfoById(form.getRecpInfoId());//获取到货信息表中数据
				
				/*
				 * 信息入退货计划表
				 */
				StringBuffer hql = new StringBuffer();
				hql.append(" insert into pur_returnplan(ret_plan_id,recp_info_id,order_code,del_detail_id,hosp_code,hosp_name,hosp_spel_code,hosp_status, ");
				hql.append(" hosp_area_id,hosp_area_name,hosp_ar_shortname,hosp_ar_fullname,user_id,goods_code,del_comp_code,del_comp_name, ");
				hql.append(" del_comp_name_spel,del_comp_status,approve_code,credential,pro_price,ret_count,ret_reason,join_ret_time,join_ret_user, ");
				hql.append(" upd_time,upd_user) ");
				hql.append(" values(:ret_plan_id, :recp_info_id, :order_code, :del_detail_id, :hosp_code, :hosp_name, :hosp_spel_code, :hosp_status, ");
				hql.append("  :hosp_area_id, :hosp_area_name, :hosp_ar_shortname, :hosp_ar_fullname, :user_id, ");
				hql.append(" :goods_code, :del_comp_code, :del_comp_name, :del_comp_name_spel, :del_comp_status, :approve_code, :credential, ");
				hql.append(" :pro_price, :ret_count, :ret_reason, :join_ret_time, :join_ret_user, :upd_time, :upd_user) ");
				
				SQLQuery query = session.createSQLQuery(hql.toString());
				
				query.setString("ret_plan_id",UUID.randomUUID().toString() );
				query.setString("recp_info_id", form.getRecpInfoId());
				query.setString("order_code", "");
				query.setString("del_detail_id", "");
				query.setString("hosp_code",form.getHospCode() );
				query.setString("hosp_name",form.getHospName() );
				query.setString("hosp_spel_code",form.getHospSpelCode() );
				query.setInteger("hosp_status",form.getHospStatus() );
				query.setString("hosp_area_id",form.getHospAreaId() );
				query.setString("hosp_area_name",form.getHospAreaName() );
				query.setString("hosp_ar_shortname",form.getHospArShortname() );
				query.setString("hosp_ar_fullname",form.getHospArFullname() );
				query.setString("user_id",aoginfo.getUserId() );
				query.setString("goods_code",aoginfo.getGoodsCode() );
				query.setString("del_comp_code",aoginfo.getDelCompCode() );
				query.setString("del_comp_name",aoginfo.getDelCompName() );
				query.setString("del_comp_name_spel",aoginfo.getDelCompNameSpel() );
				query.setInteger("del_comp_status",aoginfo.getDelCompStatus() );
				query.setString("approve_code",aoginfo.getApproveCode() );
				query.setString("credential", aoginfo.getCredential());
				query.setDouble("pro_price",aoginfo.getProPrice());
				query.setInteger("ret_count",ParseNumber.toInteger(form.getRetCount()) );
				query.setString("ret_reason",form.getRetReason() );
				query.setTimestamp("join_ret_time",new Date() );
				query.setString("join_ret_user",form.getJoinRetUser() );
				query.setTimestamp("upd_time",new Date() );
				query.setString("upd_user",form.getUpdUser() );
				query.executeUpdate();
				session.flush();
				session.clear();
				
			}
		}
	}

	/**
	 * 
	 * @category 删除退货计划（医疗机构）
	 * @author 韩守松
	 * @date 2015年12月2日 
	 * @param list
	 */
	public void deleteReturnPlan(List<ReturnDrugForm> list) {
		if (null != list && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				String sql = "DELETE from pur_returnplan where ret_plan_id=:retPlanId";
				Map<String, Object> params = new HashMap<String, Object>();
				ReturnDrugForm form = list.get(i);
				params.put("retPlanId", form.getRetPlanId());
				dao.executeSql(sql, params);
			}
		}
	}

	/**
	 * 
	 * @category 根据退货计划ID查询退货计划货信息
	 * @author 邱磊
	 * @date 2015年7月16日 上午9:53:01
	 * @param id
	 * @return
	 */
	public PurReturnPlan getReturnPlanById(String id) {
		return (PurReturnPlan) dao.getUniquerResult(PurReturnPlan.class, id);
	}

	/**
	 * 
	 * @category 保存退货计划（医疗机构）
	 * @author 韩守松
	 * @date 2015年12月3日 
	 * @param list
	 */
	public void modifyReturnPlan(List<ReturnDrugForm> list) {
		if (null != list && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				ReturnDrugForm form = list.get(i);
				PurReturnPlan data = getReturnPlanById(form.getRetPlanId());
				data.setRetCount(ParseNumber.toInteger(form.getRetCount()));
				data.setRetReason(form.getRetReason());
				data.setUpdTime(new Date());
				data.setUpdUser(form.getUpdUser());
				dao.update(data);
			}
		}
	}

	/**
	 * 
	 * @category 生成退货单（医疗机构）
	 * @author 韩守松
	 * @date 2015年12月2日 下午1:04:11
	 * @param list
	 */
	@SuppressWarnings("unchecked")
	public void createReturnInfo(ReturnDrugForm form) {
		Session session = sessionFactory.getCurrentSession();
		if (null != form) {
				List<ReturnDrugForm> planList = form.getReList();
				if (null != planList && planList.size() > 0) {
					List<String> planIdList = new LinkedList<String>();
					for (ReturnDrugForm plan : planList) {
						planIdList.add(plan.getRetPlanId());
					}
					
					Criteria criteria = session.createCriteria(PurReturnPlan.class);
					criteria.add(Restrictions.in("retPlanId", planIdList));
					List<PurReturnPlan> prList = criteria.list();//获取到生成退货单的计划id的退货计划表信息
					if (null == prList || prList.size() < 1) {
						throw new MSException(
								MSException.ALL_PLAN_ALREADY_TO_ORDER,
								"退货计划已提交，请勿重复提交！");
					} else if (form.getConfirm() == 0
							&& prList.size() != planList.size()) {
						throw new MSException(
								MSException.SOME_PLAN_ALREADY_TO_ORDER,
								"部分退货计划已提交，确定提交剩余退货计划吗？");
					}
					if (null != prList && prList.size() > 0) {
						
						Map<String, BigDecimal> delCodeAllPriceMap = new HashMap<String, BigDecimal>();//key 企业编码 value 退货总价
						Map<String,Integer> recCountMap = new HashMap<String, Integer>();//key 企业编码 value 退货数量
						
						Map<String, String> delMap = new HashMap<String, String>();//key 不同配送企业编码 value 初始条目数 
						for(int i=0;i<prList.size();i++){
							PurReturnPlan getplan = prList.get(i);
							for(int n=0;n<planList.size();n++){
								if(getplan.getRetPlanId().equals(planList.get(n).getRetPlanId())){
									getplan.setDelCompCode(planList.get(n).getDelCompCode());
									getplan.setProPrice(Double.valueOf(planList.get(n).getProPrice()));
								}
							}
							String delresult = delMap.get(getplan.getDelCompCode());
							if(StringUtils.isBlank(delresult)){//相同的企业编码
								delresult = "001";
								recCountMap.put(getplan.getDelCompCode(), Integer.valueOf(planList.get(i).getRetCount()));//不同的配送企业 退货数量汇总
								BigDecimal retCountBigDecimal = new BigDecimal(Integer.valueOf(planList.get(i).getRetCount()));
								BigDecimal priceBigDecimal = new BigDecimal(getplan.getProPrice());
								BigDecimal newAllPrice = retCountBigDecimal.multiply(priceBigDecimal);//初始企业编码的会总价
								delCodeAllPriceMap.put(getplan.getDelCompCode(), newAllPrice);//不同的配送企业 退货金额汇总
								delMap.put(getplan.getDelCompCode(), delresult);
							}else{
								Integer nextRetCount = Integer.valueOf(planList.get(i).getRetCount());
								Integer allRetCount = recCountMap.get(getplan.getDelCompCode())+nextRetCount;
								
								recCountMap.put(getplan.getDelCompCode(), allRetCount);//同一家配送企业退货数量汇总
								
								BigDecimal nextRetCounteBigDecimal = new BigDecimal(planList.get(i).getRetCount());//下一个采购数量
								BigDecimal nextpriceBigDecimal = new BigDecimal(getplan.getProPrice());//下一个采购价
								BigDecimal nextAllPriceBigDecimal = nextRetCounteBigDecimal.multiply(nextpriceBigDecimal);//下一个采购总金额
								
								BigDecimal delCodeAllPrice = delCodeAllPriceMap.get(getplan.getDelCompCode()).add(nextAllPriceBigDecimal);
								delCodeAllPriceMap.put(getplan.getDelCompCode(),delCodeAllPrice);//同一家配送企业退货金额汇总
							}
							
						}
						
						int codeIndex = 0;
						int packIndex = 0;
						String prefix = getNum();
						Map<String, String> packIndexMap = new HashMap<String, String>();
						for (int j=0;j<prList.size();j++) {
							PurReturnPlan plan = prList.get(j);
							for(int n=0;n<planList.size();n++){
								if(plan.getRetPlanId().equals(planList.get(n).getRetPlanId())){
									plan.setDelCompCode(planList.get(n).getDelCompCode());
									plan.setProPrice(Double.valueOf(planList.get(n).getProPrice()));
									plan.setRecpInfoId(planList.get(n).getRecpInfoId());
								}
							}
							codeIndex++;
							String codeIndexStr = fillZero(codeIndex, 5);//补位（长度为5）退货明细号
							String packIndexStr = packIndexMap.get(plan.getDelCompCode());//根据配送企业id缓存退货单号
							
							if (StringUtils.isBlank(packIndexStr)) {
								packIndex++;
								packIndexStr = fillZero(packIndex, 3);//退货单号
								packIndexMap.put(plan.getDelCompCode(), packIndexStr);
								/*
								 * 退货信息入退货表
								 */
								StringBuffer hql = new StringBuffer();
								hql.append(" insert into pur_returninfo(ret_code,hosp_code,hosp_name,hosp_spel_code,hosp_status,hosp_area_id, ");
								hql.append(" hosp_area_name,hosp_ar_shortname,hosp_ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel, ");
								hql.append(" del_comp_status,detail_count,detail_amount,ret_datetime,ret_user,status ) ");
								hql.append(" values( ");
								hql.append(" :ret_code  , :hosp_code , :hosp_name , :hosp_spel_code , :hosp_status , :hosp_area_id ,  ");
								hql.append(" :hosp_area_name , :hosp_ar_shortname , :hosp_ar_fullname , :del_comp_code , :del_comp_name ,");
								hql.append(" :del_comp_name_spel , :del_comp_status , :detail_count , :detail_amount , :ret_datetime , ");
								hql.append(" :ret_user , :status  ");
								hql.append(" ) ");
								
								SQLQuery query = session.createSQLQuery(hql.toString());
								query.setString("ret_code",prefix + packIndexStr);
								query.setString("hosp_code", form.getHospCode());
								query.setString("hosp_name", form.getHospName());
								query.setString("hosp_spel_code", form.getHospSpelCode());
								query.setInteger("hosp_status", form.getHospStatus());
								query.setString("hosp_area_id", form.getHospAreaId());
								query.setString("hosp_area_name", form.getHospAreaName());
								query.setString("hosp_ar_shortname", form.getHospArShortname());
								query.setString("hosp_ar_fullname", form.getHospArFullname());
								query.setString("del_comp_code", plan.getDelCompCode());
								query.setString("del_comp_name", plan.getDelCompName());
								query.setString("del_comp_name_spel", plan.getDelCompCode());
								query.setInteger("del_comp_status", plan.getDelCompStatus());
								query.setInteger("detail_count",recCountMap.get(plan.getDelCompCode()));
								query.setBigDecimal("detail_amount", delCodeAllPriceMap.get(plan.getDelCompCode()));
								query.setTimestamp("ret_datetime",new Date());
								query.setString("ret_user", form.getUpdUser());
								query.setInteger("status", ReturnStatus.submit.getKey());
								query.executeUpdate();
								session.flush();
								session.clear();
							}
							
							Map<String,Object> map = getPurAoginfoNews(plan.getRecpInfoId());
							/*
							 * 退货信息入退货明细表
							 */
							StringBuffer detailhql = new StringBuffer();
							detailhql.append(" insert into pur_returninfodetail(ret_detail_code,ret_code,recp_info_id, ");
							detailhql.append(" ret_count,ret_reason,status,upd_time,upd_user,approve_code,pro_price,total_amount,credential,goods_code, ");
							detailhql.append(" area_id,area_name,ar_shortname,ar_fullname,proj_id,proj_name,proj_starttime, ");
							detailhql.append(" proj_endtime,proj_status,qualityspec_id,drug_code,qualityspec_name,qualityspec_name_spel, ");
							detailhql.append(" qualityspec_name_eng,form_name,spec_name,qualityspec_status,bus_name,factor,unit_name,material_name,drug_medicine, ");
							detailhql.append(" prod_compcode,prod_compname,prod_compname_spel,prod_compstatus,cat_comp_code,cat_comp_name, ");
							detailhql.append(" cat_comp_name_spel,cat_comp_status,use_range,drug_attr,goods_source,pattern_type,is_healthcare,is_agriculture, ");
							detailhql.append(" is_lowprice,is_smallprice,is_smallsource,is_longtime_plan,drug_status,drug_cat,pro_price_limit,retail_price_limit,hang_price,ref_price,healthcare_price,pic_url,pic_short_url,good_status ");
							detailhql.append(" ) ");
							detailhql.append(" values( :ret_detail_code , :ret_code , :recp_info_id ,  ");
							detailhql.append(" :ret_count , :ret_reason , :status, :upd_time , :upd_user ,:approve_code,:pro_price,:total_amount,:credential,:goods_code, ");
							detailhql.append(" :area_id, :area_name, :ar_shortname, :ar_fullname, :proj_id, :proj_name, :proj_starttime, ");
							detailhql.append(" :proj_endtime, :proj_status, :qualityspec_id, :drug_code, :qualityspec_name, :qualityspec_name_spel, ");
							detailhql.append(" :qualityspec_name_eng, :form_name, :spec_name, :qualityspec_status, :bus_name, :factor, :unit_name, :material_name, :drug_medicine, ");
							detailhql.append(" :prod_compcode, :prod_compname, :prod_compname_spel, :prod_compstatus, :cat_comp_code, :cat_comp_name, ");
							detailhql.append(" :cat_comp_name_spel, :cat_comp_status, :use_range, :drug_attr, :goods_source, :pattern_type, :is_healthcare, :is_agriculture, ");
							detailhql.append(" :is_lowprice, :is_smallprice, :is_smallsource, :is_longtime_plan, :drug_status, :drug_cat, :pro_price_limit, :retail_price_limit, :hang_price, :ref_price, :healthcare_price, :pic_url, :pic_short_url, :good_status ");
							detailhql.append(" ) ");
							SQLQuery detailQuery = session.createSQLQuery(detailhql.toString());
							detailQuery.setString("ret_detail_code", prefix + packIndexStr + codeIndexStr);
							detailQuery.setString("ret_code", prefix + packIndexStr);
							detailQuery.setString("recp_info_id", plan.getRecpInfoId());
							detailQuery.setInteger("ret_count", Integer.valueOf(planList.get(j).getRetCount()));
							detailQuery.setString("ret_reason", planList.get(j).getRetReason());
							detailQuery.setInteger("status", ReturnStatus.submit.getKey());
							detailQuery.setTimestamp("upd_time", new Date());
							detailQuery.setString("upd_user", plan.getUpdUser());
							detailQuery.setParameter("approve_code", map.get("approve_code"));
							detailQuery.setParameter("pro_price", map.get("pro_price"));
							detailQuery.setParameter("total_amount", map.get("total_amount"));
							detailQuery.setParameter("credential", map.get("credential"));
							detailQuery.setString("goods_code", map.get("goods_code").toString());
							detailQuery.setString("area_id", map.get("area_id").toString());
							detailQuery.setString("area_name", map.get("area_name").toString());
							detailQuery.setString("ar_shortname", map.get("ar_shortname").toString());
							detailQuery.setString("ar_fullname", map.get("ar_fullname").toString());
							detailQuery.setString("proj_id", map.get("proj_id").toString());
							detailQuery.setString("proj_name", map.get("proj_name").toString());
							detailQuery.setDate("proj_starttime", DateUtil.parseDate(map.get("proj_starttime").toString()));
							detailQuery.setDate("proj_endtime", DateUtil.parseDate(map.get("proj_endtime").toString()));
							detailQuery.setInteger("proj_status", Integer.valueOf(map.get("proj_status").toString()));
							detailQuery.setString("qualityspec_id", map.get("qualityspec_id")==null?"":map.get("qualityspec_id").toString());
							detailQuery.setString("drug_code", map.get("drug_code")==null?"":map.get("drug_code").toString());
							detailQuery.setString("qualityspec_name", map.get("qualityspec_name")==null?"":map.get("qualityspec_name").toString());
							detailQuery.setString("qualityspec_name_spel", map.get("qualityspec_name_spel")==null?"":map.get("qualityspec_name_spel").toString());
							detailQuery.setString("qualityspec_name_eng", map.get("qualityspec_name_eng")==null?"":map.get("qualityspec_name_eng").toString());
							detailQuery.setString("form_name", map.get("form_name").toString());
							detailQuery.setString("spec_name", map.get("spec_name").toString());
							detailQuery.setInteger("qualityspec_status", Integer.valueOf(map.get("qualityspec_status").toString()));
							detailQuery.setString("bus_name", map.get("bus_name")==null?"":map.get("bus_name").toString());
							detailQuery.setString("factor", map.get("factor")==null?"":map.get("factor").toString());
							detailQuery.setString("unit_name", map.get("unit_name")==null?"":map.get("unit_name").toString());
							detailQuery.setString("material_name", map.get("material_name")==null?"":map.get("material_name").toString());
							detailQuery.setString("drug_medicine", map.get("drug_medicine")==null?"":map.get("drug_medicine").toString());
							detailQuery.setString("prod_compcode", map.get("prod_compcode")==null?"":map.get("prod_compcode").toString());
							detailQuery.setString("prod_compname", map.get("prod_compname")==null?"":map.get("prod_compname").toString());
							detailQuery.setString("prod_compname_spel", map.get("prod_compname_spel")==null?"":map.get("prod_compname_spel").toString());
							detailQuery.setInteger("prod_compstatus", Integer.valueOf(map.get("prod_compstatus").toString()));
							detailQuery.setString("cat_comp_code", map.get("cat_comp_code")==null?"":map.get("cat_comp_code").toString());
							detailQuery.setString("cat_comp_name", map.get("cat_comp_name")==null?"":map.get("cat_comp_name").toString());
							detailQuery.setString("cat_comp_name_spel", map.get("cat_comp_name_spel")==null?"":map.get("cat_comp_name_spel").toString());
							detailQuery.setInteger("cat_comp_status", Integer.valueOf(map.get("cat_comp_status").toString()));
							detailQuery.setInteger("use_range", Integer.valueOf(map.get("use_range").toString()));
							detailQuery.setInteger("drug_attr", Integer.valueOf(map.get("drug_attr").toString()));
							detailQuery.setInteger("goods_source", Integer.valueOf(map.get("goods_source").toString()));
							detailQuery.setInteger("pattern_type", Integer.valueOf(map.get("pattern_type").toString()));
							detailQuery.setInteger("is_healthcare", Integer.valueOf(map.get("is_healthcare").toString()));
							detailQuery.setInteger("is_agriculture", Integer.valueOf(map.get("is_agriculture").toString()));
							detailQuery.setInteger("is_lowprice", Integer.valueOf(map.get("is_lowprice").toString()));
							detailQuery.setInteger("is_smallprice", Integer.valueOf(map.get("is_smallprice").toString()));
							detailQuery.setInteger("is_smallsource", Integer.valueOf(map.get("is_smallsource").toString()));
							if(null != map.get("is_longtime_plan")){
								detailQuery.setInteger("is_longtime_plan", Integer.valueOf(map.get("is_longtime_plan").toString()));
							}else{
								detailQuery.setBigInteger("is_longtime_plan", null);
							}
							detailQuery.setParameter("drug_status", map.get("drug_status"));
							detailQuery.setParameter("drug_cat", map.get("drug_cat"));
							detailQuery.setParameter("pro_price_limit", map.get("pro_price_limit"));
							detailQuery.setParameter("retail_price_limit", map.get("retail_price_limit"));
							detailQuery.setParameter("hang_price",map.get("hang_price")==null?"0.00":map.get("hang_price"));
							detailQuery.setParameter("ref_price", map.get("ref_price")==null?"0.00":map.get("ref_price"));
							detailQuery.setParameter("healthcare_price", map.get("healthcare_price")==null?"0.00":map.get("healthcare_price"));
							detailQuery.setParameter("pic_url",map.get("pic_url"));
							detailQuery.setParameter("pic_short_url", map.get("pic_short_url"));
							detailQuery.setParameter("good_status", map.get("status"));
							detailQuery.executeUpdate();
							session.flush();
							session.clear();
							
							/*
							 * 更新到货表累计退货数量
							 */
							ReturnDrugForm returnCountForm = planList.get(j);
							PurAoginfo aoginfo = (PurAoginfo) dao.getUniquerResult(PurAoginfo.class, plan.getRecpInfoId());
							aoginfo.setReturnCount(returnCountForm.getReturnCount() + Integer.valueOf(returnCountForm.getRetCount()));
							dao.update(aoginfo);
							
							/*
							 * 删除退货计划表中数据
							 */
							String sql = "DELETE from pur_returnplan where ret_plan_id=:retPlanId";
							Map<String, Object> params = new HashMap<String, Object>();
							params.put("retPlanId", plan.getRetPlanId());
							dao.executeSql(sql, params);

						}
					}
				}
			}
		}

	/**
	 * 
	 * @category 获取退货单
	 * @author 邱磊
	 * @date 2015年7月16日 下午2:59:30
	 * @param page
	 * @return
	 */
	public Pagination getHospReturnInfoDataList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.ret_code retCode, a.hosp_code hospCode, a.hosp_name hospName, ");
		sb.append(" a.detail_count detailCount, a.detail_amount detailAmount,  ");
		sb.append(" DATE_FORMAT(a.ret_datetime, '%Y-%m-%d %T') retDatetime, ");
		sb.append(" a.hosp_ar_fullname areaName, a.del_comp_code delCompCode, ");
		sb.append(" a.del_comp_name delCompName, a.`status` returnStatus, a.confirm_user confirmUser, ");
		sb.append(" DATE_FORMAT(a.confirm_time, '%Y-%m-%d %T') confirmTime, b.`name` retUser ");
		sb.append(" FROM pur_returninfo a  ");
		sb.append(" LEFT JOIN sys_user b ON a.ret_user = b.id ");
		sb.append(" WHERE a.`status` = :status AND a.hosp_code = :hospCode ");
		
		ReturnOrder returnOrder = (ReturnOrder) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("status", ReturnStatus.submit.getKey());
		params.put("hospCode", returnOrder.getHospCode());
		
		if (StringUtils.isNotBlank(returnOrder.getRetCode())) {
			sb.append(" AND a.ret_code = :retCode ");
			params.put("retCode", returnOrder.getRetCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDelCompName())) {
			sb.append(" AND ( a.del_comp_name LIKE :delCompName or a.del_comp_name_spel LIKE :delCompName ) ");
			params.put("delCompName", "%" + returnOrder.getDelCompName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", returnOrder.getStartDate());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", returnOrder.getEndDate());
		}
		sb.append(" order by a.ret_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * 
	 * @category 跳向退货单详细列表页面（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月16日 下午3:34:14
	 * @param page
	 * @return
	 */
	public Pagination getHospReturnInfoDetailDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select a.ret_detail_code as retDetailCode,c.name as joinRetUser,d.comp_name as delCompName,a.drug_id as drugId,b.drug_code as drugCode,b.drug_name as drugName,")
				.append("b.form_name as formName,b.spec_name as specName,b.factor,b.unit,b.material_name as materialName,")
				.append("b.prod_comp_id as compId,b.comp_name as compName,a.lot_id as lotId,a.pro_price as proPrice,a.ret_count as retCount,a.ret_reason as retReason,DATE_FORMAT(a.submit_time,'%Y-%m-%d %T') as sbumitTime,a.status ")
				.append(" from pur_returninfo a")
				.append(" left join view_drug b on a.drug_id=b.drug_id")
				.append(" left join sys_user c on c.id=a.join_ret_user")
				.append(" left join std_compinfo d on d.comp_id=a.del_comp_id")
				.append(" where a.ret_code=:retCode");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("retCode", map.get("retCode"));
		if (StringUtils.isNotBlank(map.get("drugCode"))) {
			sql.append(" and b.drug_code=:drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank(map.get("drugName"))) {
			if (PinYin.isPinYin(map.get("drugName").trim())) {
				sql.append(" and b.drug_name_spel like :drugName");
				params.put("drugName", "%"
						+ map.get("drugName").trim().toUpperCase() + "%");
			} else {
				sql.append(" and b.drug_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(map.get("compName"))) {
			sql.append(" and b.comp_name like :compName");
			params.put("compName", "%" + map.get("compName").trim() + "%");
		}
		if (StringUtils.isNotBlank(map.get("lotId"))) {
			sql.append(" and a.lot_id=:lotId");
			params.put("lotId", map.get("lotId"));
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * @category 生成编号
	 * @date 2015年7月10日
	 * @return
	 */
	private String getNum() {
		String str = "";
		Date date = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
		str = format.format(date) + getRandom();
		return str;
	}

	/**
	 * @category 生成5位随机数不足补零
	 * @date 2015年7月10日
	 * @return
	 */
	private String getRandom() {
		int random = (int) Math.floor(Math.random() * 10000);
		return fillZero(random, 4);
	}

	/**
	 * @category 补零
	 * @return
	 */
	private String fillZero(int number, int length) {
		String f = "%0" + length + "d";
		return String.format(f, number);
	}

	/**
	 * @category list转map
	 * @date 2015年7月10日
	 * @param planList
	 * @return
	 *//*
	private Map<String, String> planListToMap(List<ReturnDrugForm> planList) {
		Map<String, String> map = new HashMap<String, String>();
		if (planList != null && !planList.isEmpty()) {
			for (ReturnDrugForm plan : planList) {
				map.put(plan.getRetPlanId(), plan.getRetCount());
				map.put("reason" + plan.getRetPlanId(), plan.getRetReason());
			}
		}
		return map;
	}*/
	
	@SuppressWarnings("unchecked")
	private Map<String,Object> getPurAoginfoNews(String recpInfoId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append(" select approve_code,credential,pro_price,total_amount,goods_code, ");
		hql.append(" area_id,area_name,ar_shortname,ar_fullname,proj_id,proj_name,proj_starttime, ");
		hql.append(" proj_endtime,proj_status,qualityspec_id,drug_code,qualityspec_name,qualityspec_name_spel, ");
		hql.append(" qualityspec_name_eng,form_name,spec_name,qualityspec_status,bus_name,factor,unit_name,material_name,drug_medicine, ");
		hql.append(" prod_compcode,prod_compname,prod_compname_spel,prod_compstatus,cat_comp_code,cat_comp_name, ");
		hql.append(" cat_comp_name_spel,cat_comp_status,use_range,drug_attr,goods_source,pattern_type,is_healthcare,is_agriculture, ");
		hql.append(" is_lowprice,is_smallprice,is_smallsource,is_longtime_plan,drug_status,drug_cat,pro_price_limit,retail_price_limit,hang_price,ref_price,healthcare_price,pic_url,pic_short_url,status ");
		hql.append("from pur_aoginfo where recp_info_id = :recpInfoId ");
		SQLQuery query  = session.createSQLQuery(hql.toString());
		query.setParameter("recpInfoId", recpInfoId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query.list();
		return list.get(0);
	}
}
